/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package reportesdeer;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Collections;
import java.util.StringTokenizer;
import model.Historico;
import java.io.OutputStream;
import java.rmi.Naming;
import java.rmi.RMISecurityManager;
import java.rmi.RemoteException;
import java.rmi.registry.LocateRegistry;
import java.rmi.registry.Registry;
import java.rmi.server.UnicastRemoteObject;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Properties;
import model.Interseccion;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;

/**
 *
 * @author Rossy
 */
public class Reportes extends UnicastRemoteObject implements IReportes {

    Properties reportesPropiedades;
    Connection connection = null;
    ResultSet result = null;
    String url;
    String user;
    String password;
    String server;

//    private final String url = "jdbc:mysql://localhost:3306/deer";
//    private final String user = "rossy";
//    private final String password = "rossy123";
    public Reportes() throws RemoteException {
        super();
        Inicializar();
    }

    public int[] generarReporte1(String dia, int horaInicial, int horaFinal) throws java.rmi.RemoteException {
        int arrayPatron[];
        arrayPatron = new int[24];
        //inicializar
        for (int i = 0; i < 24; i++) {
            arrayPatron[i] = 0;
        }

        try {
            connection = this.getConnection();
            if (connection != null) {
                Statement select = connection.createStatement();
                PreparedStatement pstmt = null;
                String query = null;

                if (dia.length() > 0) {
                    //Realiza la búsqueda por día
                    query = "select * from patron where " + dia + " like '%1%' ;";
                    pstmt = connection.prepareStatement(query);
                    result = pstmt.executeQuery();
                    while (result.next()) {
                        int zona= result.getInt("zona");
                        String algoritmo = result.getString(dia);
                        //horas del patron
                        for (int i = horaInicial; i <= horaFinal; i++) {
                            if (algoritmo.charAt(i) == '1') {
                                arrayPatron[i]++;
                            }
                        }
                    }
                }
                result.close();
                result = null;
                closeConnection();
            } //fin del if(connection!=null)
        } catch (Exception e) {
            e.printStackTrace();
        }
        return arrayPatron;
    }

    public ArrayList<Historico> generarReporte2(String dia) throws java.rmi.RemoteException {
        ArrayList<Historico> historicoList = new ArrayList<Historico>();
        ArrayList<Historico> historicoOrdenado = new ArrayList<Historico>();
        Historico historico;
        int cantVehiculos = 0;
        int cant;

        try {
            connection = this.getConnection();
            if (connection != null) {
                Statement select = connection.createStatement();
                PreparedStatement pstmt = null;
                String query = null;

                if (dia.length() > 0) {
                    //Realiza la búsqueda por día
                    query = "select zona,"+dia+" from historico";
                    pstmt = connection.prepareStatement(query);
                    result = pstmt.executeQuery();
                    while (result.next()) {
                        String vehiculos = result.getString(dia);
                        StringTokenizer tokens = new StringTokenizer(vehiculos, ",");
                        while (tokens.hasMoreTokens()) {
                            cant = Integer.parseInt(tokens.nextToken());
                            cantVehiculos = cantVehiculos + cant;
                        }
                        historico = new Historico();
                        historico.setId(result.getInt("zona"));
                        historico.setCant(cantVehiculos);
                        historicoList.add(historico);
                        cantVehiculos = 0;
                    }

                }
                result.close();
                result = null;
                closeConnection();
                historicoOrdenado = ordenarListaHistorico(historicoList);
            } //fin del if(connection!=null)

        } catch (Exception e) {
            e.printStackTrace();
        }
        return historicoOrdenado;
    }

    public ArrayList<Historico> ordenarListaHistorico(ArrayList<Historico> historicoList) {
        Collections.sort(historicoList, Historico.HistoricoVehiculosComparator);
        Collections.reverse(historicoList);
        return historicoList;
    }

    public int generarReporte3(String filename) throws java.rmi.RemoteException {
        ArrayList<Interseccion> interseccionList = new ArrayList<Interseccion>();
        Interseccion interseccion;
        int respuesta=0;
        
        try {
            connection = this.getConnection();
            if (connection != null) {
                Statement select = connection.createStatement();
                PreparedStatement pstmt = null;
                String query = null;

                query = "select distinct(zona) zona,count(*) cantidad from interseccion group by zona";
                pstmt = connection.prepareStatement(query);
                result = pstmt.executeQuery();
                while (result.next()) {
                    interseccion = new Interseccion();
                    interseccion.setZona(result.getInt("zona"));
                    interseccion.setCant(result.getInt("cantidad"));
                    interseccionList.add(interseccion);
                }
                result.close();
                result = null;
                closeConnection();
            } //fin del if(connection!=null)

            if (interseccionList.size() > 0) {
                String filePath = filename;
                ExcelGenerator excel = new ExcelGenerator();
                excel.constructAndSaveExcel(filePath, interseccionList);
            }
            respuesta=1;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return respuesta;
    }

    private class ExcelGenerator {

        public final static short LAST_ROW_NR_OF_HEADER = 6;//Sixth row

        /**
         * Creates a new instance of BUserExcelGenerator
         */
        public ExcelGenerator() {
        }

        /**
         * Constructs an excel file and saves it in the selected directory.
         *
         * @param filePath Path where the file will be saved.
         */
        public void constructAndSaveExcel(String filePath, ArrayList<Interseccion> interseccionList) {
            OutputStream outputStream = null;
            HSSFWorkbook wb = new HSSFWorkbook();

            HSSFSheet sheet = wb.createSheet("Users");
            sheet.setDefaultColumnWidth((short) 20);

            // REPORT HEADER
//            UXLSUtil.constructReportHeader(sheet, "ZONAS x INTERSECCIONES");
            UXLSUtil xlsUtil = new UXLSUtil();
            xlsUtil.constructReportHeader(sheet, "ZONAS x INTERSECCIONES");

            constructHeader(wb, sheet);
            constructData(wb, sheet, interseccionList);

            // Create XLS
            xlsUtil.saveXLS(wb, filePath);
        }//end constructAndSaveExcel

        private void constructHeader(HSSFWorkbook wb, HSSFSheet sheet) {
            HSSFColor.WHITE colorWhite = new HSSFColor.WHITE();
            HSSFFont headerFont = wb.createFont();
            headerFont.setFontHeightInPoints((short) 8);
            headerFont.setColor(colorWhite.getIndex());
            headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

            HSSFColor.BLUE colorBlue = new HSSFColor.BLUE();
            HSSFCellStyle headerStyle = wb.createCellStyle();
            headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//        headerStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);
            headerStyle.setFillBackgroundColor(IndexedColors.BLUE.getIndex());
            headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
//        headerStyle.setFillBackgroundColor(colorBlue.getIndex());
            headerStyle.setFont(headerFont);

            HSSFRow row = sheet.createRow(LAST_ROW_NR_OF_HEADER); // Header goes on Third Row

            HSSFCell cell_0 = row.createCell((short) 0);
            cell_0.setCellValue("Zona");
            cell_0.setCellStyle(headerStyle);

            HSSFCell cell_1 = row.createCell((short) 1);
            cell_1.setCellValue("Num. Intersecciones");
            cell_1.setCellStyle(headerStyle);
        }//end constructHeader

        private void constructData(HSSFWorkbook wb, HSSFSheet sheet, ArrayList<Interseccion> interseccionList) {
            HSSFColor.BLACK colorBlack = new HSSFColor.BLACK();
            HSSFFont dataFont = wb.createFont();
            dataFont.setFontHeightInPoints((short) 8);
            dataFont.setColor(colorBlack.getIndex());

            HSSFCellStyle dataStyle = wb.createCellStyle();
            dataStyle.setBorderBottom((short) HSSFCellStyle.BORDER_THIN);
            dataStyle.setBorderLeft((short) HSSFCellStyle.BORDER_THIN);
            dataStyle.setBorderRight((short) HSSFCellStyle.BORDER_THIN);
            dataStyle.setBorderTop((short) HSSFCellStyle.BORDER_THIN);
            dataStyle.setFont(dataFont);

            Interseccion interseccion = null;
            int dataStartingRow = LAST_ROW_NR_OF_HEADER + 1;
            HSSFRow row = null;
            HSSFCell cell_0, cell_1;

            for (int i = 0; i < interseccionList.size(); i++) {
                interseccion = interseccionList.get(i);
                row = sheet.createRow((int) (dataStartingRow + i));

                cell_0 = row.createCell((short) 0);
                cell_0.setCellValue(interseccion.getZona());
                dataStyle.setAlignment(dataStyle.ALIGN_LEFT);
                cell_0.setCellStyle(dataStyle);

                cell_1 = row.createCell((short) 1);
                cell_1.setCellValue(interseccion.getCant());
                dataStyle.setAlignment(dataStyle.ALIGN_LEFT);
                cell_1.setCellStyle(dataStyle);
            }//end for
        }//end constructData

    }

    private class UXLSUtil {

        /**
         * Creates a new instance of UUtil
         */
        public UXLSUtil() {
        }

        public void constructReportHeader(HSSFSheet sheet, String reportTitle) {
            // Report Title
            HSSFRow row = sheet.createRow((short) 0);
            row.createCell((short) 0).setCellValue(reportTitle);

            row = sheet.createRow((short) 2);
            row.createCell((short) 0).setCellValue("Date:");
            row.createCell((short) 1).setCellValue(getTodayDDMMYYYY());

            row = sheet.createRow((short) 3);
            row.createCell((short) 0).setCellValue("Time:");
            row.createCell((short) 1).setCellValue(getTodayHHMMSS());
        }//constructReportHeader

        ////////////////////////////////////////////////////////////////////////////////////////////////////
        public void saveXLS(HSSFWorkbook wb, String filePath) {
            OutputStream outputStream = null;
            try {
                // Write the output
                outputStream = new FileOutputStream(filePath+".xls");
                wb.write(outputStream);
                outputStream.close();

                StringTokenizer strTokFile = new StringTokenizer(filePath, File.separator);
                String path = "";
                String name = "";
                int maxTokens = strTokFile.countTokens();
                int i = 1;
                while (strTokFile.hasMoreTokens()) {
                    if (i < maxTokens) {
                        path += strTokFile.nextToken() + File.separator;
                    } else {
                        name = strTokFile.nextToken();
                    }
                    i++;
                }

                System.out.println("The report file with name \"" + name + "\" has been succesfully saved \n"
                        + "in the directory \"" + path + "\".");
            } catch (Exception e) {
                e.printStackTrace();
                System.out.println("The file has not been saved properly");
            }
        }//end saveXLS

        public String getTodayDDMMYYYY() {
            Calendar calendar = Calendar.getInstance();
            int year, month, day;
            String strYear, strMonth, strDay;

            year = calendar.get(Calendar.YEAR);
            strYear = Integer.toString(year);

            month = calendar.get(Calendar.MONTH);
            month++;
            strMonth = Integer.toString(month);
            strMonth = padNumberWithCeros(strMonth, 2);

            day = calendar.get(Calendar.DATE);
            strDay = Integer.toString(day);
            strDay = padNumberWithCeros(strDay, 2);
            return (strDay + "/" + strMonth + "/" + strYear);
        }//end getTodayDDMMYYYY

        public String getTodayHHMMSS() {
            Calendar calendar = Calendar.getInstance();
            int hour, minute, second;
            String strHour, strMinute, strSecond;

            hour = calendar.get(Calendar.HOUR_OF_DAY);
            strHour = Integer.toString(hour);
            strHour = padNumberWithCeros(strHour, 2);

            minute = calendar.get(Calendar.MINUTE);
            strMinute = Integer.toString(minute);
            strMinute = padNumberWithCeros(strMinute, 2);

            second = calendar.get(Calendar.SECOND);
            strSecond = Integer.toString(second);
            strSecond = padNumberWithCeros(strSecond, 2);

            return (strHour + ":" + strMinute + ":" + strSecond);
        }//end getTodayHHMMSS

        public String padNumberWithCeros(String strNumber, int totalDigits) {
            int digits = strNumber.length();
            String pad = "";
            int number = -99;
            if (validateInteger(strNumber)) {
                number = Integer.parseInt(strNumber);
                if (number >= 0) {
                    for (; digits < totalDigits; digits++) {
                        pad += "0";
                    }
                }
            }
            return (pad + strNumber);
        }

        public boolean validateInteger(String strInteger) {
            if (strInteger.equalsIgnoreCase("")) {
                return false;
            }
            try {
                int number = Integer.parseInt(strInteger);
            } catch (Exception e) {
                return false;
            }
            return true;
        }//end validateInteger

    }

    public int[] generarReporte4(String dia, int horaInicial, int horaFinal) throws java.rmi.RemoteException {
        int arrayVehiculos[];
        arrayVehiculos = new int[24];
        int pos=0;
        int cantVehiculos;
        //inicializar
        for (int i = 0; i < 24; i++) {
            arrayVehiculos[i] = 0;
        }

        try {
            connection = this.getConnection();
            if (connection != null) {
                Statement select = connection.createStatement();
                PreparedStatement pstmt = null;
                String query = null;

                if (dia.length() > 0) {
                    //Realiza la búsqueda por día
                    query = "select zona,"+dia+" from historico where "+dia+"<>'0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,';"; //+ " like '%1%' ;";
                    pstmt = connection.prepareStatement(query);
                    result = pstmt.executeQuery();
                    while (result.next()) {
                        int zona= result.getInt("zona");
                        String vehiculos = result.getString(dia);
                        StringTokenizer tokens = new StringTokenizer(vehiculos, ",");
                        while (tokens.hasMoreTokens()) {
                            cantVehiculos = Integer.parseInt(tokens.nextToken());
                            if((pos>=horaInicial)&&(pos<=horaFinal))
                                arrayVehiculos[pos]=arrayVehiculos[pos]+cantVehiculos;
                            if (pos>horaFinal)  break;
                            pos++;
                        }
                        pos=0;
                    }
                }
                result.close();
                result = null;
                closeConnection();
            } //fin del if(connection!=null)
        } catch (Exception e) {
            e.printStackTrace();
        }
        return arrayVehiculos;
    }

    
    private String getConnectionUrl() {
        return url;
    }

    private java.sql.Connection getConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Connection: " + getConnectionUrl());
            connection = java.sql.DriverManager.getConnection(getConnectionUrl(),
                    user, password);
            if (connection != null) {
                System.out.println("Connection Successful!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    private void closeConnection() {
        try {
            if (connection != null) {
                System.out.println("\nClosing connection...");
                connection.close();
            }
            connection = null;
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void Inicializar() {
        this.reportesPropiedades = new PropiedadesReportes().getProperties();
        url = this.reportesPropiedades.getProperty("url");
        user = this.reportesPropiedades.getProperty("user");
        password = this.reportesPropiedades.getProperty("password");
        server = this.reportesPropiedades.getProperty("server");
    }
    //RMI Servidor
    public static void main(String[] args) {
        try {
            Registry reg = LocateRegistry.createRegistry(1099);
            reg.bind("server", new Reportes());
//            reg.rebind("//192.168.0.35:1099/Reportes", new Reportes());
            
            System.out.println("Server started!");
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}